require "md5"
require 'net/http'
require 'iconv'
require "time"
require "date"
require 'gd2'
include GD2

class DesktopController < ApplicationController
  skip_before_filter	:verify_authenticity_token 
	before_filter :login_required, :except => [:get_czxx, :get_qzxx]
	
  def index
  end
  
  def get_house
		user =	Users.find_by_sql("select *  from houses order by id;")
		size = user.size;
		if size > 0 
			txt = "{results:#{size},rows:["
			for k in 0..user.size-1
				txt = txt + user[k].to_json[9..-2] + ','
			end
			txt = txt[0..-2] + "]}"
		else
			txt = "{results:0,rows:[]}"	 
		end	 
		render :text => txt
	end
	
	
	def getPrev(tabname, colname, ygxm)
	  yg_id = Users.find_by_sql("select id from Users where login='#{ygxm}';")[0].id
	  user = Users.find_by_sql("select #{colname} from prev where tabname='#{tabname}' and yg_id = #{yg_id}")
	  #logger.debug("=======prevcode  for #{tabname}, #{colname}, #{ygxm}: #{user[0][colname]}")
	  user[0][colname]
	end
	
	def get_czxx
	  
    params['time'] = '全部房源' if (params['time'].nil?)
    params['jylx'] = '租售'   if (params['jylx'].nil?)
    params['zt']   = ''    if (params['zt'].nil?)
    params['search'] = ''   if (params['search'].nil?)
    #params['ygxm'] = '' if (params['ygxm'].nil?)
    params['qy'] = '' if (params['qy'].nil?)
    
	  query_str = "select * from czxx "

	  if params['time'] == '今日新房' 
	    days = 1
	  elsif  params['time'] == '最近三天'
	    days = 3                     
	  elsif  params['time'] == '最近七天'
      days = 7                     
	  elsif  params['time'] == '最近半月'
      days = 15                    
	  elsif  params['time'] == '最近一月'
      days = 30                    
	  elsif  params['time'] == '最近三月'
      days = 90                    
	  elsif  params['time'] == '最近半年'
      days = 180                   
	  elsif  params['time'] == '全部房源'
      days = 1000000
	  end  	    
	  
	  if !params['date1'].nil? and (params['date1'] != params['date2']) 
	    query_str = query_str + " where date(create_at) >= date('#{params['date1']}') and date(create_at) <= date('#{params['date2']}') "
	  else
	    query_str = query_str + " where CURRENT_DATE - date(create_at) < #{days} "
	  end
	  
	  if !params['mj1'].nil?
	    query_str = query_str + " and zmj >= #{params['mj1']} "
	  end
	  
	  if !params['mj2'].nil? and params['mj2'] != '不限' and params['mj2'] != params['mj1']
	    query_str = query_str + " and zmj <= #{params['mj2']} "
	  end    
	  
	  if params['jylx'] != '租售'
	    query_str = query_str + " and jy = '#{params['jylx']}' "
	  end

    if params['zt'] != '' && params['zt'] != '全部'
      ss=params['zt'].split('+')
      if ss.count == 2 
        query_str = query_str + " and (zt = '#{ss[0]}' or zt = '#{ss[1]}') "
      else
        query_str = query_str + " and zt ='#{ss[0]}' "
      end    
    end
    
    if params['search'] != ''
      query_str = query_str + " and (khdh||czbh||bz||khxm||wymc||dzwz) like '%#{params['search']}%'"
    end  
    
    #if params['ygxm'] != '' && params['ygxm'] != '全部'
    #  query_str = query_str + " and ygxm = '#{params['ygxm']}'"
    #end

    if params['qy'] != '' && params['qy'] != '全部'
      query_str = query_str + " and cq = '#{params['qy']}'"
    end
       
	  
	  user = Users.find_by_sql(query_str + " order by update_at desc, id desc;")
		size = user.size;
		if size > 0 
			txt = "{results:#{size},rows:["
			
			user = Users.find_by_sql(query_str + " order by process asc, update_at desc, id desc limit #{params['limit']} offset #{params['start']};")
			size = user.size;
			for k in 0..user.size-1
				txt = txt + user[k].to_json[9..-2] + ','
			end
			txt = txt[0..-2] + "]}"
		else
			txt = "{results:0,rows:[]}"	 
		end	 
		render :text => txt
	end
	
	def get_czgj
	  if params['id'].nil? || params['id'] == ""
	    txt = "{results:0,rows:[]}"	 
	  else
  		user =	Users.find_by_sql("select *  from czgj where czxx_id = #{params['id']} order by id;")
  		size = user.size;
  		if size > 0 
  			txt = "{results:#{size},rows:["
  			for k in 0..user.size-1
  				txt = txt + user[k].to_json[9..-2] + ','
  			end
  			txt = txt[0..-2] + "]}"
  		else
  			txt = "{results:0,rows:[]}"	 
  		end
  	end		 
		render :text => txt	  
	end
	
	def get_qzgj
	  if params['id'].nil? || params['id'] == ""
	    txt = "{results:0,rows:[]}"	 
	  else
  		user =	Users.find_by_sql("select * from qzgj where qzxx_id = #{params['id']} order by id;")
  		size = user.size;
  		if size > 0 
  			txt = "{results:#{size},rows:["
  			for k in 0..user.size-1
  				txt = txt + user[k].to_json[9..-2] + ','
  			end
  			txt = txt[0..-2] + "]}"
  		else
  			txt = "{results:0,rows:[]}"	 
  		end
  	end		 
		render :text => txt	  
	end
	  
	def save_czxx
	  
	  time_str = Time.now.strftime('%Y-%m-%d %H:%M:%S')
	  
	  if params['kh_id'].nil? || params['kh_id'] == ''
	    params['kh_id'] = Users.find_by_sql("select max(id) from customer;")[0].max.to_i+1
	    
	    new_id = Users.find_by_sql("select kh_bm from xxbm where id=1;")[0].kh_bm.to_i+1
	    Users.find_by_sql("update xxbm set kh_bm = #{new_id} where id=1;")
	    khbh = 'C'+new_id.to_s.rjust(5).gsub(' ', '0')
	    Users.find_by_sql("insert into customer(id, khbh, khlb, gsmc,  mobile, lxr, khsfz, address, create_at, yg_id) values (#{params['kh_id']}, '#{khbh}', '出租客户', '#{params['khxm']}', '#{params['khdh']}', '#{params['khlxr']}', '#{params['khsfz']}', '#{params['dzwz']}', '#{time_str}', #{params['yg_id']} );".gsub("请选择", ""))
	  end   
    
    process = ''
    process = '✗' if !params['gs'].nil?
    process = '✓' if !params['yzk'].nil?
    
	  params['update_at'] = time_str
	  if params['id'].nil? || params['id'] == ''
	    
	    new_id = Users.find_by_sql("select cz_bm from xxbm where id=1;")[0].cz_bm.to_i+1
	    Users.find_by_sql("update xxbm set cz_bm = #{new_id} where id=1;")
	    params['czbh'] = 'CZ'+new_id.to_s.rjust(5).gsub(' ', '0')
	    
	    Users.find_by_sql("insert into czxx (bgmj, bz, cfmj, cq, create_at, cs, czbh, czfs, dgl, dzwz, fwcq, fwly, fwpt, fwss, fwst, fwwq, fwxz, fwzj, fyfs, glf, gs, height, jfsj, jy, kdmj, kf, kh_id, khdh, khgj, khlxr, khsfz, khxm, lc, length, lx, mpdh, pq, sdj, ssmj, szj, tax, width, wtbh, wtdq, wtfs, wymc, wyzd_id, yg_id, ygbm, ygxm, yt, yzf, zc, zdj, zmj, zt, zzj, update_at, process,lxfs) values( #{params['bgmj']}, '#{params['bz']}', #{params['cfmj']}, '#{params['cq']}', TIMESTAMP '#{params['create_at']}', '#{params['cs']}', '#{params['czbh']}','#{params['czfs']}', '#{params['dgl']}', '#{params['dzwz']}', '#{params['fwcq']}', '#{params['fwly']}', '#{params['fwpt']}', '#{params['fwss']}', '#{params['fwst']}', '#{params['fwwq']}', '#{params['fwxz']}', '#{params['fwzj']}', '#{params['fyfs']}', '#{params['glf']}', '#{params['gs']}', #{params['height']}, '#{params['jfsj']}', '#{params['jy']}', #{params['kdmj']}, '#{params['kf']}', #{params['kh_id']}, '#{params['khdh']}', '#{params['khgj']}', '#{params['khlxr']}', '#{params['khsfz']}', '#{params['khxm']}', #{params['lc']}, #{params['length']}, '#{params['lx']}', '#{params['mpdh']}', '#{params['pq']}', #{params['sdj']}, #{params['ssmj']}, #{params['szj']}, '#{params['tax']}', #{params['width']}, '#{params['wtbh']}', '#{params['wtdq']}', '#{params['wtfs']}', '#{params['wymc']}', #{params['wyzd_id']}, #{params['yg_id']}, '#{params['ygbm']}', '#{params['ygxm']}', '#{params['yt']}', '#{params['yzf']}', #{params['zc']}, #{params['zdj']}, #{params['zmj']}, '#{params['zt']}', #{params['zzj']}, TIMESTAMP '#{params['update_at']}', '#{process}', '#{params['lxfs']}');".gsub("请选择", ""))
	    
	    #update customer cz_code 
	  else  
	    Users.find_by_sql("update czxx set bgmj = #{params['bgmj']}, bz = '#{params['bz']}', cfmj = #{params['cfmj']}, cq = '#{params['cq']}', create_at = TIMESTAMP '#{params['create_at']}', cs = '#{params['cs']}', czfs = '#{params['czfs']}', dgl = '#{params['dgl']}', dzwz = '#{params['dzwz']}', fwcq = '#{params['fwcq']}', fwly = '#{params['fwly']}', fwpt = '#{params['fwpt']}', fwss = '#{params['fwss']}', fwst = '#{params['fwst']}', fwwq = '#{params['fwwq']}', fwxz = '#{params['fwxz']}', fwzj = '#{params['fwzj']}', fyfs = '#{params['fyfs']}', glf = '#{params['glf']}', gs = '#{params['gs']}', height = #{params['height']}, jfsj = '#{params['jfsj']}', jy = '#{params['jy']}', kdmj = #{params['kdmj']}, kf = '#{params['kf']}', kh_id = #{params['kh_id']}, khdh = '#{params['khdh']}', khgj = '#{params['khgj']}', khlxr = '#{params['khlxr']}', khsfz = '#{params['khsfz']}', khxm = '#{params['khxm']}', lc = #{params['lc']}, length = #{params['length']}, lx = '#{params['lx']}', mpdh = '#{params['mpdh']}', pq = '#{params['pq']}', sdj = #{params['sdj']}, ssmj = #{params['ssmj']}, szj = #{params['szj']}, tax = '#{params['tax']}', width = #{params['width']}, wtbh = '#{params['wtbh']}', wtdq = '#{params['wtdq']}', wtfs = '#{params['wtfs']}', wymc = '#{params['wymc']}', wyzd_id = #{params['wyzd_id']},  yt = '#{params['yt']}', yzf = '#{params['yzf']}', zc = #{params['zc']}, zdj = #{params['zdj']}, zmj = #{params['zmj']}, zt = '#{params['zt']}', zzj = #{params['zzj']}, update_at = TIMESTAMP '#{params['update_at']}', process='#{process}', lxfs='#{params['lxfs']}' where id=#{params['id']};".gsub("请选择", ""))
	     
	     #yg_id = #{params['yg_id']}, ygbm = '#{params['ygbm']}', ygxm = '#{params['ygxm']}',
	     #update user information at the same time
	     Users.find_by_sql("update customer set gsmc='#{params['khxm']}', lxr='#{params['khlxr']}', mobile='#{params['khdh']}', address='#{params['cq']} #{params['pq']} #{params['dzwz']}', khsfz='#{params['khsfz']}' where id=#{params['kh_id']};".gsub("请选择", ""))
	  end
	  
	  render :text => "Success|#{new_id}|#{params['czbh']}"
	end
	
	def delete_czxx
	  Users.find_by_sql("delete from czxx where id = #{params['id']};")
	  render :text => 'Success'
	end
	
	def add_czgj
	  Users.find_by_sql("insert into czgj(czxx_id, create_at, yg_id, ygbm, ygxm, message, txsj, txfw, txnr) values(#{params['czxx_id']},  TIMESTAMP '#{params['create_at']}', #{params['yg_id']}, '#{params['ygbm']}', '#{params['ygxm']}', '#{params['gjnr']}', TIMESTAMP '#{params['gjrq']} #{params['gjsj']}', '#{params['txfw']}', '#{params['txnr']}' );")
	  
	  params['update_at'] = Time.now.strftime('%Y-%m-%d %H:%M:%S')
	  Users.find_by_sql("update czxx set update_at=TIMESTAMP '#{params['update_at']}' where id=#{params['czxx_id']};")
	  
	  render :text => 'Success'
	end
	
	def delete_czgj
	  Users.find_by_sql("delete from czgj where id=#{params['id']};")
	  render :text => 'Sucess'
	end
	
	def add_qzgj
	  Users.find_by_sql("insert into qzgj(qzxx_id, create_at, yg_id, ygbm, ygxm, message, txsj, txfw, txnr) values(#{params['qzxx_id']},  TIMESTAMP '#{params['create_at']}', #{params['yg_id']}, '#{params['ygbm']}', '#{params['ygxm']}', '#{params['gjnr']}', TIMESTAMP '#{params['gjrq']} #{params['gjsj']}','#{params['txfw']}', '#{params['txnr']}' );")
	  
	  params['update_at'] = Time.now.strftime('%Y-%m-%d %H:%M:%S')
	  Users.find_by_sql("update qzxx set update_at=TIMESTAMP '#{params['update_at']}' where id=#{params['qzxx_id']};")
	  
	  render :text => 'Success'
	end
	
	def delete_qzgj
	  Users.find_by_sql("delete from qzgj where id=#{params['id']};")
	  render :text => 'Sucess'
	end
	
	def add_dkgj
	  Users.find_by_sql("insert into dkgj(dkht, qzxx_id, czxx_id, yg_id, yg_xm, qz_xm, cz_xm, visit_at, create_at, message) values('#{params['dkht']}', #{params['kyxx']}, #{params['czxx_id']}, #{params['yg_id']}, '#{params['yg_xm']}',  '#{params['qz_xm']}', '#{params['cz_xm']}', TIMESTAMP '#{params['visit_at']}', TIMESTAMP '#{params['create_at']}', '#{params['message']}');")
	  
	  params['update_at'] = Time.now.strftime('%Y-%m-%d %H:%M:%S')
	  Users.find_by_sql("update czxx set update_at=TIMESTAMP '#{params['update_at']}' where id=#{params['czxx_id']};")
	  
	  Users.find_by_sql("update qzxx set update_at=TIMESTAMP '#{params['update_at']}' where id=#{params['qzxx_id']};")
	  
	  render :text => 'Success'
	end
	
	def add_dkgj_qz
	  Users.find_by_sql("insert into dkgj(dkht, qzxx_id, czxx_id, yg_id, yg_xm, qz_xm, cz_xm, visit_at, create_at, message) values('#{params['dkht']}', #{params['qzxx_id']}, #{params['fyxx']},  #{params['yg_id']}, '#{params['yg_xm']}',  '#{params['qz_xm']}', '#{params['cz_xm']}', TIMESTAMP '#{params['visit_at']}', TIMESTAMP '#{params['create_at']}', '#{params['message']}');")
	  
	  params['update_at'] = Time.now.strftime('%Y-%m-%d %H:%M:%S')
	  Users.find_by_sql("update czxx set update_at=TIMESTAMP '#{params['update_at']}' where id=#{params['czxx_id']};")
	  
	  Users.find_by_sql("update qzxx set update_at=TIMESTAMP '#{params['update_at']}' where id=#{params['qzxx_id']};")
	  
	  render :text => 'Success'
	end
	
	def delete_czgj
	  Users.find_by_sql("delete from czgj where id=#{params['id']};")
	  render :text => 'Sucess'
	end
	
	def delete_dkgj
	  Users.find_by_sql("delete from dkgj where id=#{params['id']};")
	  render :text => 'Sucess'
	end
	
	def current_user
		render :text => current_users.to_json
	end
	
	
	def get_bm
	  user = Users.find_by_sql("select distinct bm from users;")
	  if user.size > 0
  	  txt = "["
  	  for k in 0..user.size-1
  	    txt = txt + "['#{user[k].bm}','#{user[k].bm}'],"
  	  end  
  	  txt = txt[0..-2] + "]"
	  else
	    txt = "[]"
	  end  
	  render :text => txt
	end	
	
	def get_yg
	  bm = params['bm'];
	  
	  user = Users.find_by_sql("select distinct login from users where bm='#{bm}';")
	  if user.size > 0
  	  txt = "["
  	  for k in 0..user.size-1
  	    txt = txt + "['#{user[k].login}','#{user[k].login}'],"
  	  end  
  	  txt = txt[0..-2] + "]"
	  else
	    txt = "[]"
	  end  
	  render :text => txt
	end
		
	def get_ygtree
		text = []
		node = params["node"].chomp
		if node == "root"
			data = Users.find_by_sql("select distinct bm from users;")
			data.each do |dd|
				text << {:text => dd["bm"], :id => dd["bm"], :cls	 => "folder"}
			end
		else
			pars = node.split('|') || []
	
			if pars.length == 1
				data = Users.find_by_sql("select distinct login,mobile from users where bm=\'#{pars[0]}\';")
				data.each do |dd|
					text << {:text => dd["login"], :id => pars[0]+"|#{dd["login"]}|#{dd['mobile']}", :iconCls => "user",  :leaf => true}
				end			
			end
		end
		render :text => text.to_json
	end	 
	
	def get_khtree_old
		text = []
		node = params["node"].chomp
		if node == "root"
			data = Users.find_by_sql("select distinct khxz from khxx;")
			data.each do |dd|
				text << {:text => dd["khxz"], :id => dd["khxz"], :cls	 => "folder"}
			end
		else
			pars = node.split('|') || []
	
			if pars.length == 1
				data = Users.find_by_sql("select distinct khxm,khdh from khxx where khxz=\'#{pars[0]}\';")
				data.each do |dd|
					text << {:text => dd["khxm"], :id => pars[0]+"|#{dd["khxm"]}|#{dd['khdh']}", :iconCls => "user",  :leaf => true}
				end			
			end
		end
		render :text => text.to_json
	end

	def get_khtree
    if params['filter'].nil?
      gsmc = "%%"
    else
      gsmc = "%#{params['filter']}%"
    end
        
		text = []
		node = params["node"].chomp
		if node == "root"
			data = Users.find_by_sql("select distinct khlb from customer where khlb!='';")
			data.each do |dd|
				text << {:text => dd["khlb"], :id => dd["khlb"], :cls	 => "folder"}
			end
		else
			pars = node.split('|') || []
	
			if pars.length == 1
				data = Users.find_by_sql("select id, gsmc, mobile from customer where khlb=\'#{pars[0]}\' and yg_id=#{params['yg_id']} and gsmc like '#{gsmc}';")
				data.each do |dd|
					text << {:text => dd["gsmc"]+"(#{dd['mobile']})", :id => pars[0]+"|#{dd["id"]}|#{dd['mobile']}", :iconCls => "user",  :leaf => true}
				end			
			end
		end
		render :text => text.to_json
	end

	
	def urlencode(str)
    str.gsub(/[^a-zA-Z0-9_\.\-]/n) {|s| sprintf('%%%02x', s[0]) }
  end
  
	def send_sms
	  send_id="papas#{Time.now.to_i}"
	  #content = urlencode(params['sms_text'])
	  content = Iconv.conv('gb2312', 'utf-8', params['sms_text'])
	  
	  sendstr="ID=#{send_id}&UserName=papas&Key=b078481f87ee9cd2&SendNum=#{params['receiver']}&Content=#{content}&SendTiming=0&SendTime="
	  logger.debug("#{send_id}papasb078481f87ee9cd2#{params['receiver']}#{content}0")
    
    md5str = MD5.hexdigest("#{send_id}papasb078481f87ee9cd2#{params['receiver']}#{content}0").upcase
    logger.debug(md5str)
    
    #url="http://sms.powereasy.net/MessageGateUTF8/Message.aspx?#{sendstr}&MD5String=#{md5str}"
    #logger.debug(url)
    
    data = "#{sendstr}&MD5String=#{md5str}"
    logger.debug(data)

    @http = Net::HTTP.new('sms.powereasy.net', 80)
    headers = {
      #'Cookie' => cookie,
      'Referer' => 'http://sms.powereasy.net/',
      'Content-Type' => 'application/x-www-form-urlencoded'
    }
    resp, data = @http.post('/MessageGate/Message.aspx', data, headers)
    logger.debug(resp)
    logger.debug(data)
     
	  render :text => 'Success'
	end
	
	#房客信息
	def get_qzxx
	  
    params['time'] = '全部客源' if (params['time'].nil?)
    params['jylx'] = '租购'   if (params['jylx'].nil?)
    params['zt']   = ''    if (params['zt'].nil?)
    params['search'] = ''   if (params['search'].nil?)
    #params['ygxm'] = ''   if (params['ygxm'].nil?)     
    
    if (params['ygxm'].nil?)
      params['ygxm'] = ''
    elsif getPrev('客源管理', 'advprev', params['ygxm']) == 't'
      params['ygxm'] = ''
    end

	  query_str = "select * from qzxx "

	  if params['time'] == '今日新客' 
	    days = 1
	  elsif  params['time'] == '最近三天'
	    days = 3                     
	  elsif  params['time'] == '最近七天'
      days = 7                     
	  elsif  params['time'] == '最近半月'
      days = 15                    
	  elsif  params['time'] == '最近一月'
      days = 30                    
	  elsif  params['time'] == '最近三月'
      days = 90                    
	  elsif  params['time'] == '最近半年'
      days = 180                   
	  elsif  params['time'] == '全部客源'
      days = 1000000
	  end  	    
	  
	  if !params['date1'].nil? and (params['date1'] != params['date2']) 
	    query_str = query_str + " where date(create_at) >= date('#{params['date1']}') and date(create_at) <= date('#{params['date2']}') "
	  else
	    query_str = query_str + " where CURRENT_DATE - date(create_at) < #{days}"
	  end
	  
	  if !params['mj1'].nil?
	    query_str = query_str + " and cf_min > #{params['mj1']} "
	  end
	  
	  if !params['mj2'].nil? and params['mj2'] != '不限'
	    query_str = query_str + " and cf_max < #{params['mj2']} "
	  end
	  
	  query_str = query_str + " and ygxm = '#{params['ygxm']}' " if (params['ygxm'] != '')

	  logger.debug("1"+query_str)
	  
	  if params['jylx'] != '租购'
	    query_str = query_str + " and jy = '#{params['jylx']}'"
	  end

    if params['zt'] != '' && params['zt'] != '全部'
      ss=params['zt'].split('+')
      if ss.count == 2 
        query_str = query_str + " and (zt = '#{ss[0]}' or zt = '#{ss[1]}')"
      else
        query_str = query_str + " and zt ='#{ss[0]}' "
      end    
    end
    
    if params['search'] != ''
      query_str = query_str + "and (kybh||bz||khxm||khdh||cq||yt) like '%#{params['search']}%'"
    end  
	  
	  logger.debug("2"+query_str)
	  
	  user = Users.find_by_sql(query_str + " order by update_at desc, id desc;")
		size = user.size;
		if size > 0 
			txt = "{results:#{size},rows:["
			
			user = Users.find_by_sql(query_str + " order by process asc, update_at desc, id desc limit #{params['limit']} offset #{params['start']};")
			size = user.size;

			for k in 0..user.size-1
				txt = txt + user[k].to_json[9..-2] + ','
			end
			txt = txt[0..-2] + "]}"
		else
			txt = "{results:0,rows:[]}"	 
		end	 
		render :text => txt
	end
	
	
		
	def save_qzxx
	  time_str = Time.now.strftime('%Y-%m-%d %H:%M:%S')
	  if params['kh_id'].nil? || params['kh_id'] == ''
	    params['kh_id'] = Users.find_by_sql("select max(id) from customer;")[0].max.to_i+1
	    
	    new_id = Users.find_by_sql("select kh_bm from xxbm where id=1;")[0].kh_bm.to_i+1
	    Users.find_by_sql("update xxbm set kh_bm = #{new_id} where id=1;")
	    khbh = 'C'+new_id.to_s.rjust(5).gsub(' ', '0')
	    Users.find_by_sql("insert into customer(id, khbh, khlb, gsmc,  mobile, lxr, khsfz, address, create_at, yg_id) values (#{params['kh_id']}, '#{khbh}', '求租客户','#{params['khxm']}', '#{params['khdh']}', '#{params['khlxr']}', '#{params['khsfz']}', '#{params['khdz']}', '#{time_str}', #{params['yg_id']} );".gsub("请选择", ""))
	  end   
    
    process = ''
    process = '✗' if !params['gs'].nil?
    process = '✓' if !params['yzk'].nil?
    
    
	  params['update_at'] = time_str
	  if params['id'] == ""
	    new_id = Users.find_by_sql("select qz_bm from xxbm where id=1;")[0].qz_bm.to_i+1
	    Users.find_by_sql("update xxbm set qz_bm = #{new_id} where id=1;")
	    params['kybh'] = 'K'+new_id.to_s.rjust(5).gsub(' ', '0')
	    
	    Users.find_by_sql("insert into qzxx (bgmj, bz, cf_max, cf_min, cq, create_at, cs, czfs, dgl, fwpt, fwwq, fyfs, gs, jg_max, jg_min, jy, kdmj, khdh, khdj, khdz, khgj, khlb, khlxr, khqx, khsfz, khxm, khxz, kh_id, kybh, lx, mpdh, pq, qzyx, ssmj, update_at, wtfs, wtly, ygbm, ygmj, ygxm, yg_id, yt, yzk, zt, process, lxfs) values( #{params['bgmj']}, '#{params['bz']}', #{params['cf_max']}, #{params['cf_min']}, '#{params['cq']}', '#{params['create_at']}', '#{params['cs']}', '#{params['czfs']}', '#{params['dgl']}', '#{params['fwpt']}', '#{params['fwwq']}', '#{params['fyfs']}', '#{params['gs']}', #{params['jg_max']}, #{params['jg_min']}, '#{params['jy']}', #{params['kdmj']}, '#{params['khdh']}', '#{params['khdj']}', '#{params['khdz']}', '#{params['khgj']}', '#{params['khlb']}', '#{params['khlxr']}', '#{params['khqx']}', '#{params['khsfz']}', '#{params['khxm']}', '#{params['khxz']}', #{params['kh_id']}, '#{params['kybh']}', '#{params['lx']}', '#{params['mpdh']}', '#{params['pq']}', '#{params['qzyx']}', #{params['ssmj']}, '#{params['update_at']}', '#{params['wtfs']}', '#{params['wtly']}', '#{params['ygbm']}', #{params['ygmj']}, '#{params['ygxm']}', #{params['yg_id']}, '#{params['yt']}', '#{params['yzk']}', '#{params['zt']}', '#{process}', '#{params['lxfs']}');".gsub("请选择", ""))
	  else  
	     Users.find_by_sql("update qzxx set bgmj = #{params['bgmj']}, bz = '#{params['bz']}', cf_max = #{params['cf_max']}, cf_min = #{params['cf_min']}, cq = '#{params['cq']}', create_at = '#{params['create_at']}', cs = '#{params['cs']}', czfs = '#{params['czfs']}', dgl = '#{params['dgl']}', fwpt = '#{params['fwpt']}', fwwq = '#{params['fwwq']}', fyfs = '#{params['fyfs']}', gs = '#{params['gs']}', jg_max = #{params['jg_max']}, jg_min = #{params['jg_min']}, jy = '#{params['jy']}', kdmj = #{params['kdmj']}, khdh = '#{params['khdh']}', khdj = '#{params['khdj']}', khdz = '#{params['khdz']}', khgj = '#{params['khgj']}', khlb = '#{params['khlb']}', khlxr = '#{params['khlxr']}', khqx = '#{params['khqx']}', khsfz = '#{params['khsfz']}', khxm = '#{params['khxm']}', khxz = '#{params['khxz']}', kh_id = #{params['kh_id']}, kybh = '#{params['kybh']}', lx = '#{params['lx']}', mpdh = '#{params['mpdh']}', pq = '#{params['pq']}', qzyx = '#{params['qzyx']}', ssmj = #{params['ssmj']}, update_at = '#{params['update_at']}', wtfs = '#{params['wtfs']}', wtly = '#{params['wtly']}',yt = '#{params['yt']}', ygmj = #{params['ygmj']},  yzk = '#{params['yzk']}', zt = '#{params['zt']}', process = '#{process}', lxfs = '#{params['lxfs']}' where id=#{params['id']} ;".gsub("请选择", ""))
	     
	     # ygbm = '#{params['ygbm']}', ygxm = '#{params['ygxm']}', yg_id = #{params['yg_id']}, 
	     #update user information at the same time
	     Users.find_by_sql("update customer set gsmc='#{params['khxm']}', lxr='#{params['khlxr']}', mobile='#{params['khdh']}', address='#{params['khdz']}', khsfz='#{params['khsfz']}' where id=#{params['kh_id']};".gsub("请选择", ""))
	  end
	  render :text => 'Success'
	end
		
	def delete_qzxx
	  Users.find_by_sql("delete from qzxx where id = #{params['id']};")
	  render :text => 'Success'
	end	
		
	def get_khxx
	  if params['filter'].nil?
		  user = Users.find_by_sql("select *  from khxx order by id;")
		else
		  user = Users.find_by_sql("select * from khxx where position('#{params['filter']}' in khxm || khlxr || khdh || khsfz || khgj || khdz) > 0 order by id;")
		end  
		size = user.size;
		if size > 0 
			txt = "{results:#{size},rows:["
			for k in 0..user.size-1
				txt = txt + user[k].to_json[9..-2] + ','
			end
			txt = txt[0..-2] + "]}"
		else
			txt = "{results:0,rows:[]}"	 
		end	 
		render :text => txt	  
	end
	
	def add_khxx
	  time = Time.now.strftime("%Y-%m-%d %H:%M:%S")
	  if params['kh_id'].nil? || params['kh_id'] == ''
	    Users.find_by_sql("insert into khxx (khxm, khdh, khlxr, khsfz, khdz, khgj, create_at, yg_id, yg_xm) values ('#{params['khxm']}', '#{params['khdh']}', '#{params['khlxr']}', '#{params['khsfz']}', '#{params['khdz']}', '#{params['khgj']}', '#{time}',  #{params['yg_id']}, '#{params['yg_xm']}');")
	  else
	    Users.find_by_sql("update khxx set khxm='#{params['khxm']}', khdh='#{params['khdh']}', khlxr='#{params['khlxr']}', khsfz='#{params['khsfz']}', khdz='#{params['khdz']}', khgj='#{params['khgj']}' where id = #{params['kh_id']};")
	  end  
	  render :text => 'Success'
	end
	
	def delete_khxx
 	  Users.find_by_sql("delete from khxx where id = #{params['id']};")
	  render :text => 'Success'
	end  
	
	
	def get_dkgj
	  if params['id'].nil? || params['id'] == ""
	    txt = "{results:0,rows:[]}"	 
	  else
  		user =	Users.find_by_sql("select dkgj.*, czbh from dkgj inner join czxx on dkgj.czxx_id = czxx.id where czxx_id = #{params['id']} order by id;")
  		size = user.size;
  		if size > 0 
  			txt = "{results:#{size},rows:["
  			for k in 0..user.size-1
  				txt = txt + user[k].to_json[9..-2] + ','
  			end
  			txt = txt[0..-2] + "]}"
  		else
  			txt = "{results:0,rows:[]}"	 
  		end
  	end		 
		render :text => txt	  
	end  	

	def get_dkgj_qz
	  if params['id'].nil? || params['id'] == ""
	    txt = "{results:0,rows:[]}"	 
	  else
  		user =	Users.find_by_sql("select dkgj.*, czbh from dkgj inner join czxx on dkgj.czxx_id = czxx.id where qzxx_id = #{params['id']} order by id;")
  		size = user.size;
  		if size > 0 
  			txt = "{results:#{size},rows:["
  			for k in 0..user.size-1
  				txt = txt + user[k].to_json[9..-2] + ','
  			end
  			txt = txt[0..-2] + "]}"
  		else
  			txt = "{results:0,rows:[]}"	 
  		end
  	end		 
		render :text => txt	  
	end  	

	
	#cars
	def get_statistic_data
	  url = params['url']
	  
	  user = Users.find_by_sql("select distinct \"name\", sum(count) from cars group by \"name\";")
    place = Users.find_by_sql("select distinct place from cars;")
    
    for k in 0..user.count-1 do
    	for kk in 0..place.count-1 do
    		value = Users.find_by_sql("select sum(count) from cars where \"name\"='#{user[k].name}' and place='#{place[kk].place}';")
    		user[k][place[kk].place]=value[0].sum 
    	end
    end

    body="<TR height='25px'>\n"+"<TH class='table_title' width=100>车名</TH>\n"
    for k in 0..place.count-1 do 
      body = body + "<TH class='table_title' width=100>#{place[k].place}</TH>\n"
    end
    body = body + "<TH class='table_title' width=100>总数</TH>\n"
    body = body +"</TR>\n"

    for k in 0..user.count-1 do
      if (k % 2) == 1
        td = "<TD class='td_cell_1'>"
      else
        td = "<TD class='td_cell_2'>"
      end
        
      body = body + "<TR>\n"
      body = body + "#{td}#{user[k].name}</TD>\n"
      for kk in 0..place.count-1 do
          body = body + "#{td}#{user[k][place[kk].place]}</TD>\n"
      end  
      body = body + "#{td}#{user[k].sum}</TD>\n" 
      body = body + "<TR>\n" 
    end  
    
    txt = {:title => url, :body => body }
    render :text => txt.to_json
	end

  def get_statistic_cq_yg
    url, title = params['url'], params['text']
    time1, time2=Time.parse(params['start_date']), Time.parse(params['end_date'])
    date1, date2=Date.parse(params['start_date']), Date.parse(params['end_date'])
    
    body = ''
    
    table_name = 'qzxx' if url=~/ky.yg..b/  
    table_name = 'czxx' if url=~/fy.yg..b/
    table_name = 'qzgj' if url=~/kygj.yg..b/
    table_name = 'czgj' if url=~/fygj.yg..b/
    table_name = 'dkgj' if url=~/kf.yg..b/
    
    if url=~/rb$/  #房源.员工.日报 CZXX
      period = ((time2-time1)/86400).to_i

      body="<TR height='25px'>\n"+"<TH class='table_title' width=100>员工</TH>\n"
      sums=[]
    
      for days in 0..period do 
        tt = time1+86400*days
        date = tt.strftime("%m-%d")
        body = body + "<TH class='table_title' width=100>#{date}</TH>\n"
        sums[days]=0
      end
      body = body + "<TH class='table_title' width=100>总数</TH>\n"
      body = body +"</TR>\n"  

      user = Users.find_by_sql("select distinct id, login from Users where status = TRUE order by id;")
      for k in 0..user.count-1 do
        id, login = user[k].id, user[k].login
        if (k % 2) == 1
          td = "<TD class='td_cell_1'>"
        else
          td = "<TD class='td_cell_2'>"
        end

        body = body + "<TR>\n"
        body = body + "#{td}#{login}</TD>\n"

        sum_p = 0
        for days in 0..period
          tt = time1 + days*86400
          date = tt.strftime("%Y-%m-%d")
          count = Users.find_by_sql("select count(*) from #{table_name} where yg_id=#{id} and date(create_at)='#{date}';")[0].count
          sums[days] = sums[days]+count.to_i
          sum_p = sum_p+count.to_i
          body = body + "#{td}#{count}</TD>\n"
        end
        body = body + "#{td}#{sum_p}</TD>\n" 
        body = body + "<TR>\n"
      end

      #add Total bottom
      body = body + "<TR>\n<TD class='td_cell_0'></TD>"
      sum_all=0
      for k in 0..period do 
        body = body + "<TD class='td_cell_0' width=100>#{sums[k]}</TD>\n"
        sum_all = sum_all+sums[k]
      end   
      
      body = body + "<TD class='td_cell_0'>#{sum_all}</TD>\n<TR>\n"
    
    end

    if url=~/zb$/  #房源.员工.周报 CZXX
      
      period = ((time2-time1)/604800).to_i

      body="<TR height='25px'>\n"+"<TH class='table_title' width=100>员工</TH>\n"
      sums=[]
    
      for days in 0..period do 
        tt1, tt2 = time1+604800*days, time1+604800*(days+1)-86400
        date = tt1.strftime("%m/%d")+'~'+tt2.strftime("%m/%d")
        body = body + "<TH class='table_title' width=100>#{date}</TH>\n"
        sums[days]=0
      end
      body = body + "<TH class='table_title' width=100>总数</TH>\n"
      body = body +"</TR>\n"  

      user = Users.find_by_sql("select distinct id, login from Users where status = TRUE order by id;")
      for k in 0..user.count-1 do
        id, login = user[k].id, user[k].login
        if (k % 2) == 1
          td = "<TD class='td_cell_1'>"
        else
          td = "<TD class='td_cell_2'>"
        end

        body = body + "<TR>\n"
        body = body + "#{td}#{login}</TD>\n"

        sum_p = 0
        for days in 0..period
          tt1, tt2 = time1+604800*days, time1+604800*(days+1)-86400
          date1, date2 = tt1.strftime("%Y-%m-%d"), tt2.strftime("%Y-%m-%d")
          count = Users.find_by_sql("select count(*) from #{table_name} where yg_id=#{id} and date(create_at)>='#{date1}' and date(create_at)<='#{date2}';")[0].count
          sums[days] = sums[days]+count.to_i
          sum_p = sum_p+count.to_i
          body = body + "#{td}#{count}</TD>\n"
        end
        body = body + "#{td}#{sum_p}</TD>\n" 
        body = body + "<TR>\n"
      end

      #add Total bottom
      body = body + "<TR>\n<TD class='td_cell_0'></TD>"
      sum_all=0
      for k in 0..period do 
        body = body + "<TD class='td_cell_0' width=100>#{sums[k]}</TD>\n"
        sum_all = sum_all+sums[k]
      end   
      
      body = body + "<TD class='td_cell_0'>#{sum_all}</TD>\n<TR>\n"     
      
    end  
    
    if url=~/yb$/  #房源.员工.月报 CZXX
      
      dt1=Date.parse(date1.strftime("%Y-%m-01"))
      dt2=((dt1-1)>>1)
      
      period = ((time2-time1)/86400/30).to_i
      
      body="<TR height='25px'>\n"+"<TH class='table_title' width=100>员工</TH>\n"
      sums=[]
    
      for days in 0..period do 
        tt1, tt2 = dt1>>days, dt2>>days
        date = tt1.strftime("%Y/%m")
        body = body + "<TH class='table_title' width=100>#{date}</TH>\n"
        sums[days]=0
      end
      body = body + "<TH class='table_title' width=100>总数</TH>\n"
      body = body +"</TR>\n"  

      user = Users.find_by_sql("select distinct id, login from Users where status = TRUE order by id;")
      for k in 0..user.count-1 do
        id, login = user[k].id, user[k].login
        if (k % 2) == 1
          td = "<TD class='td_cell_1'>"
        else
          td = "<TD class='td_cell_2'>"
        end

        body = body + "<TR>\n"
        body = body + "#{td}#{login}</TD>\n"

        sum_p = 0
        for days in 0..period
          logger.debug("date1: #{dt1.to_s}, date2: #{dt2.to_s}, period:#{period}")
          tt1, tt2 = dt1>>days, dt2>>days
          date1, date2 = tt1.strftime("%Y-%m-%d"), tt2.strftime("%Y-%m-%d")
          count = Users.find_by_sql("select count(*) from #{table_name} where yg_id=#{id} and date(create_at)>='#{date1}' and date(create_at)<='#{date2}';")[0].count
          sums[days] = sums[days]+count.to_i
          sum_p = sum_p+count.to_i
          body = body + "#{td}#{count}</TD>\n"
        end
        body = body + "#{td}#{sum_p}</TD>\n" 
        body = body + "<TR>\n"
      end

      #add Total bottom
      body = body + "<TR>\n<TD class='td_cell_0'></TD>"
      sum_all=0
      for k in 0..period do 
        body = body + "<TD class='td_cell_0' width=100>#{sums[k]}</TD>\n"
        sum_all = sum_all+sums[k]
      end   
      
      body = body + "<TD class='td_cell_0'>#{sum_all}</TD>\n<TR>\n"     
    
    end
      
    txt = {:title => title, :body => body }
    render :text => txt.to_json
    
  end
  
	def get_statistic_cq_yg_old
	  url, title = params['url'], params['text']
	  
	  user = Users.find_by_sql("select distinct cq, count(*) as sum from czxx where cq != '' group by cq order by sum desc;")
    #place = Users.find_by_sql("select distinct ygxm as place from czxx;")
    
    place = Users.find_by_sql("select distinct ygxm as place, count(*) as sum from czxx where cq != '' group by ygxm order by sum desc;")
    
    for k in 0..user.count-1 do
    	for kk in 0..place.count-1 do
    		value = Users.find_by_sql("select count(*) as sum from czxx where cq='#{user[k].cq}' and ygxm='#{place[kk].place}';")
    		user[k][place[kk].place]=value[0].sum 
    	end
    end

    body="<TR height='25px'>\n"+"<TH class='table_title' width=400>区县</TH>\n"
    for k in 0..place.count-1 do 
      body = body + "<TH class='table_title' width=100>#{place[k].place}</TH>\n"
    end
    body = body + "<TH class='table_title' width=100>总数</TH>\n"
    body = body +"</TR>\n"

    for k in 0..user.count-1 do
      if (k % 2) == 1
        td = "<TD class='td_cell_1'>"
      else
        td = "<TD class='td_cell_2'>"
      end
        
      body = body + "<TR>\n"
      body = body + "#{td}#{user[k].cq}</TD>\n"
      for kk in 0..place.count-1 do
          body = body + "#{td}#{user[k][place[kk].place]}</TD>\n"
      end  
      body = body + "#{td}#{user[k].sum}</TD>\n" 
      body = body + "<TR>\n" 
    end  
    
    #add Total bottom
    body = body + "<TR>\n<TD class='td_cell_0'></TD>"
    for k in 0..place.count-1 do 
      body = body + "<TD class='td_cell_0' width=100>#{place[k].sum}</TD>\n"
    end   
    body = body + "<TD class='td_cell_0'></TD>\n<TR>\n" 
    
    txt = {:title => title, :body => body }
    render :text => txt.to_json
	end

	
	def get_statistic_data1
	  url = params['url']
	  body = "<TR height='25px'>
    <TH class='table_title' width=100></TH>
    <TH class='table_title' width=100>germany</TH>
    <TH class='table_title' width=100>czech rep.</TH>
    <TH class='table_title' width=100></TH>
    </TR>
    <TR>
    <TD>bmw</TD>
    <TD>1000</TD>
    <TD>100</TD>
    <TD>1100</TD>
    </TR>
    <TD>skoda</TD>
    <TD>5000</TD>
    <TD>10000</TD>
    <TD>15000</TD>
    </TR>
    <TD>opel</TD>
    <TD>7000</TD>
    <TD>7000</TD>
    <TD>14000</TD>
    </TR>"
	  txt = {:title => url, :body => body }
	  render :text => txt.to_json
	end
	
	
	def check_khxx_mobile
	  mobile = params['mobile'].gsub(/\s+/, '')
	  user = Users.find_by_sql("select * from customer where mobile = '#{mobile}';")
	  render :text => user.to_json
	end
	
	def set_customer
    if !params['khxx_id'].nil?
      user = Users.find_by_sql("select * from customer where id = #{params['khxx_id']};")
      if user.size > 0 
        txt = user.to_json
      else
        txt = "[]"
      end  
      render :text => txt
      return 
    end  
    
	  if !params['qzxx_id'].nil? 
	    user = Users.find_by_sql("select kh_id from qzxx where id = #{params['qzxx_id']};")
	  else
	    user = Users.find_by_sql("select kh_id from czxx where id = #{params['czxx_id']};")
	  end
      
	  if user.size > 0
	    kh_id = user[0].kh_id.to_i
      user = Users.find_by_sql("select * from customer where id = #{kh_id};")
      if user.size > 0 
        txt = user.to_json
      end     
	  else
	    txt = "[]"
	  end
	  render :text => txt  
	end
	
	def add_customer
    if params['id'].nil? || params['id'] == ''  #need insert this record 
      new_id = Users.find_by_sql("select kh_bm from xxbm where id=1;")[0].kh_bm.to_i+1
	    Users.find_by_sql("update xxbm set kh_bm = #{new_id} where id=1;")
	    params['khbh'] = 'KH'+new_id.to_s.rjust(6).gsub(' ', '0')
      
	    user = Users.find_by_sql("select kh_id from qzxx where id = #{params['qzxx_id']};")
	    kh_id = user[0].kh_id.to_i
	    Users.find_by_sql("insert into customer (id, khbh, khlb, khjb, gsmc, bgdh1, bgdh2, bgcz, gsweb, zip, address, syzlx, zczj, gsgm, gsqm, khyh, yhzh, swdjh, lxr, zw, hy, mobile,iphone, linktone, email, jtdh1, jtdh2, jtdz, birthday, children, hobby, jtdnp,bz ) values (#{kh_id}, '#{params['khbh']}', '#{params['khlb']}', '#{params['khjb']}', '#{params['gsmc']}', '#{params['bgdh1']}', '#{params['bgdh2']}','#{params['bgcz']}','#{params['gsweb']}','#{params['zip']}','#{params['address']}','#{params['syzlx']}','#{params['zczj']}','#{params['gsgm']}','#{params['gsqm']}','#{params['khyh']}','#{params['yhzh']}','#{params['swdjh']}','#{params['lxr']}','#{params['zw']}','#{params['hy']}','#{params['mobile']}','#{params['iphone']}','#{params['linktone']}','#{params['email']}','#{params['jtdh1']}','#{params['jtdh2']}','#{params['jtdz']}','#{params['birthday']}','#{params['children']}','#{params['hobby']}','#{params['jtdnp']}','#{params['bz']}');")
	  else                                        #need update this record
	    Users.find_by_sql("update customer set khbh='#{params['khbh']}', khlb='#{params['khlb']}',khjb='#{params['khjb']}', gsmc='#{params['gsmc']}', bgdh1='#{params['bgdh1']}', bgdh2='#{params['bgdh2']}', bgcz='#{params['bgcz']}', gsweb='#{params['gsweb']}', zip='#{params['zip']}', address='#{params['address']}', syzlx='#{params['syzlx']}', zczj='#{params['zczj']}', gsgm='#{params['gsgm']}', gsqm='#{params['gsqm']}', khyh='#{params['khyh']}', yhzh='#{params['yhzh']}', swdjh='#{params['swdjh']}', lxr='#{params['lxr']}', zw='#{params['zw']}', hy='#{params['hy']}', mobile='#{params['mobile']}',iphone='#{params['iphone']}', linktone='#{params['linktone']}', email='#{params['email']}', jtdh1='#{params['jtdh1']}', jtdh2='#{params['jtdh2']}',  jtdz='#{params['jtdz']}', birthday='#{params['birthday']}', children='#{params['children']}', hobby='#{params['hobby']}', jtdnp='#{params['jtdnp']}',bz='#{params['bz']}' where id = #{params['id']};")
	  end
	  
	  render :text => 'Success'  
	end
	
	def update_customer 
	  if !params['id'].nil?
	    Users.find_by_sql("update customer set address='#{params['address']}', birthday=TIMESTAMP '#{params['birthday']}', children='#{params['children']}', bz='#{params['bz']}', email='#{params['email']}', bgcz='#{params['bgcz']}',bgdh1='#{params['bgdh1']}',bgdh2='#{params['bgdh2']}',gsgm='#{params['gsgm']}',gsmc='#{params['gsmc']}',gsqm='#{params['gsqm']}',gsweb='#{params['gsweb']}',hobby='#{params['hobby']}',hy='#{params['hy']}',iphone='#{params['iphone']}',jtdh1='#{params['jtdh1']}',jtdh2='#{params['jtdh2']}',jtdnp='#{params['jtdnp']}',jtdz='#{params['jtdz']}',khbh='#{params['khbh']}',khjb='#{params['khjb']}',khlb='#{params['khlb']}',khyh='#{params['khyh']}',lxr='#{params['lxr']}',mobile='#{params['mobile']}',swdjh='#{params['swdjh']}',syzlx='#{params['syzlx']}',zip='#{params['zip']}',zw='#{params['zw']}' where id=#{params['id']};") 
	  end
	  render :text => 'Sucess'  
	end  
	
	def add_wyzd
	  #wyzd_id = params['id']
	  wybh = params['wybh']
	  if wybh == "" 
      new_id = Users.find_by_sql("select wyzd_bm from xxbm where id=1;")[0].wyzd_bm.to_i+1
	    Users.find_by_sql("update xxbm set wyzd_bm = #{new_id} where id=1;")
	    params['wybh'] = 'WY'+new_id.to_s.rjust(5).gsub(' ', '0')
	    Users.find_by_sql("insert into wyzd (bz, cfms, create_at, dlkd, dlmp, dmcx, dmkd, dmsd, fczh, kfmj, kypdzl, mwfmj, pdfmj, pdzl, qy, syhy, symj, tdmj, tdnx, tdxz, tdzh, wybh, wymc, xfdj, xxly, ygxm, yzmc, zdfdl, zds, yg_id) values ('#{params['bz']}', '#{params['cfms']}', TIMESTAMP '#{params['create_at']}', #{params['dlkd']}, '#{params['dlmp']}', '#{params['dmcx']}', #{params['dmkd']}, #{params['dmsd']},'#{params['fczh']}', #{params['kfmj']}, #{params['kypdzl']}, #{params['mwfmj']}, #{params['pdfmj']}, #{params['pdzl']}, '#{params['qy']}', '#{params['syhy']}', #{params['symj']}, #{params['tdmj']}, #{params['tdnx']}, '#{params['tdxz']}', '#{params['tdzh']}', '#{params['wybh']}', '#{params['wymc']}', '#{params['xfdj']}', '#{params['xxly']}', '#{params['ygxm']}','#{params['yzmc']}', #{params['zdfdl']}, #{params['zds']}, #{params['yg_id']});")
	  else  
	    Users.find_by_sql("update wyzd set  bz='#{params['bz']}', cfms='#{params['cfms']}', create_at=TIMESTAMP '#{params['create_at']}', dlkd=#{params['dlkd']}, dlmp='#{params['dlmp']}', dmcx='#{params['dmcx']}', dmkd=#{params['dmkd']}, dmsd=#{params['dmsd']}, fczh='#{params['fczh']}', kfmj=#{params['kfmj']}, kypdzl=#{params['kypdzl']}, mwfmj=#{params['mwfmj']}, pdfmj=#{params['pdfmj']}, pdzl=#{params['pdzl']}, qy='#{params['qy']}', syhy='#{params['syhy']}', symj=#{params['symj']}, tdmj=#{params['tdmj']}, tdnx=#{params['tdnx']}, tdxz='#{params['tdxz']}', tdzh='#{params['tdzh']}', wybh='#{params['wybh']}', wymc='#{params['wymc']}', xfdj='#{params['xfdj']}', xxly='#{params['xxly']}', ygxm='#{params['ygxm']}',yzmc='#{params['yzmc']}', zdfdl=#{params['zdfdl']}, zds=#{params['zds']} where wybh = '#{params['wybh']}';")
	  end
	  #user = Users.find_by_sql("select * from wyzd where wybh = '#{params['wybh']}';")
	  render :text =>  params['wybh'] 
	end
	
	
	def get_wydd
	  wyzd_id = params['wyzd_id']
	  user = Users.find_by_sql("select * from wydd where wyzd_id = #{params['wyzd_id']};")
		if user.size > 0 
			txt = "{results:#{user.size},rows:["
			for k in 0..user.size-1
				txt = txt + user[k].to_json[9..-2] + ','
			end
			txt = txt[0..-2] + "]}"
		else
			txt = "{results:0,rows:[]}"	 
		end	 
		render :text => txt
	end
	
	
	def add_wydd
	  ddxx = ActiveSupport::JSON.decode params['data']
	  wyzd_id = params['wyzd_id']
	  
	  for k in 0..ddxx.size-1 do 
	    dd = ddxx[k]
	    if dd['id'] == ''
	      Users.find_by_sql("insert into wydd (wyzd_id, dh, cs, cfmj, ssmj, bgmj, kdmj, cc, kk, gg, hc, ht, bz) values(#{wyzd_id}, #{dd['dh']}, #{dd['cs']}, #{dd['cfmj']}, #{dd['ssmj']}, #{dd['bgmj']}, #{dd['kdmj']}, #{dd['cc']}, #{dd['kk']}, #{dd['gg']}, #{dd['hc']}, #{dd['ht']}, '#{dd['bz']}' );")
	    else
	      Users.find_by_sql("update wydd set  dh=#{dd['dh']}, cs=#{dd['cs']}, cfmj=#{dd['cfmj']}, ssmj=#{dd['ssmj']}, bgmj=#{dd['bgmj']}, kdmj=#{dd['kdmj']}, cc=#{dd['cc']}, kk=#{dd['kk']}, gg=#{dd['gg']}, hc=#{dd['hc']}, ht=#{dd['ht']}, bz='#{dd['bz']}' where id = #{dd['id']} ;")
	    end
	  end

	  render :text => "Success"
	end    
	
	
	def get_wyzd
	  if params['filter'].nil?
		  user = Users.find_by_sql("select id, wybh, wymc, qy, ygxm from wyzd order by id limit 50;")
		else
		  user = Users.find_by_sql("select id, wybh, wymc, ygxm from wyzd where position('#{params['filter']}' in wybh || wymc || qy || ygxm || bz ) > 0 order by id limit 50;")
		end  
		size = user.size;
		if size > 0 
			txt = "{results:#{size},rows:["
			for k in 0..user.size-1
				txt = txt + user[k].to_json[9..-2] + ','
			end
			txt = txt[0..-2] + "]}"
		else
			txt = "{results:0,rows:[]}"	 
		end	 
		render :text => txt	  
	end
	
	def get_wyzd_by_id
	  user = Users.find_by_sql("select * from wyzd where id = #{params['wyzd_id']};")
	  render :text => user.to_json
	end
	
	def get_wyzd_by_wybh
	  user = Users.find_by_sql("select * from wyzd where wybh = '#{params['wybh']}';")
	  render :text => user.to_json
	end
	
	def pub_wydd
	  wydd = ActiveSupport::JSON.decode(params['data'])
	  czxx_id = params['czxx_id']
	  
	  #find czxx_id
    user = Users.find_by_sql("select wyzd_id, czbh from czxx where id = #{czxx_id}")
    
    if user[0].czbh.include?('-')
      czbh = user[0].czbh.split('-')[0]
      czxx_id = Users.find_by_sql("select id from czxx where czbh='#{czbh}';")[0].id
      czbh = czbh+'-'+wydd['dh']
    else
      czbh = user[0].czbh+'-'+wydd['dh']
    end
    
    wyzd_id = user[0].wyzd_id  
    
    #query czbh see if is it exits
    record = Users.find_by_sql("select id from czxx where czbh='#{czbh}';") 
    
    if record.size > 0 
      update_at = Time.now.strftime('%Y-%m-%d %H:%M:%S')
      Users.find_by_sql("update czxx set cs=#{wydd['cs']}, ssmj=#{wydd['ssmj']}, kdmj=#{wydd['kdmj']}, cfmj=#{wydd['cfmj']}, bgmj=#{wydd['bgmj']}, length=#{wydd['cc']}, width=#{wydd['kk']}, height=#{wydd['gg']}, update_at =  TIMESTAMP '#{update_at}', bz='#{wydd['bz']}' where czbh='#{czbh}';")
      
       Users.find_by_sql("update wydd set published = true, cs=#{wydd['cs']}, cc=#{wydd['cc']}, kk=#{wydd['kk']}, gg=#{wydd['gg']},bgmj=#{wydd['bgmj']}, ssmj=#{wydd['ssmj']}, kdmj=#{wydd['kdmj']}, cfmj=#{wydd['cfmj']},bz='#{wydd['bz']}' where id = #{wydd['id']};")
       
    else 
      params = Users.find_by_sql("select * from czxx where id = #{czxx_id};")[0]
      params['update_at'] = Time.now.strftime('%Y-%m-%d %H:%M:%S')
      params['czbh'] = czbh
  	  params['length'] = wydd['cc']
  	  params['width']  = wydd['kk']
  	  params['height'] = wydd['gg']
  	  params['bgmj']   = wydd['bgmj']
  	  params['ssmj']   = wydd['ssmj']
  	  params['kdmj']   = wydd['kdmj']
  	  params['cfmj']   = wydd['cfmj']
  	  params['bz']     = wydd['bz']
  	  
      Users.find_by_sql("insert into czxx (bgmj, bz, cfmj, cq, create_at, cs, czbh, czfs, dgl, dzwz, fwcq, fwly, fwpt, fwss, fwst, fwwq, fwxz, fwzj, fyfs, glf, gs, height, jfsj, jy, kdmj, kf, kh_id, khdh, khgj, khlxr, khsfz, khxm, lc, length, lx, mpdh, pq, sdj, ssmj, szj, tax, width, wtbh, wtdq, wtfs, wymc, wyzd_id, yg_id, ygbm, ygxm, yt, yzf, zc, zdj, zmj, zt, zzj, update_at) values( #{params['bgmj']}, '#{params['bz']}', #{params['cfmj']}, '#{params['cq']}', TIMESTAMP '#{params['create_at']}', '#{params['cs']}', '#{params['czbh']}','#{params['czfs']}', '#{params['dgl']}', '#{params['dzwz']}', '#{params['fwcq']}', '#{params['fwly']}', '#{params['fwpt']}', '#{params['fwss']}', '#{params['fwst']}', '#{params['fwwq']}', '#{params['fwxz']}', '#{params['fwzj']}', '#{params['fyfs']}', '#{params['glf']}', '#{params['gs']}', #{params['height']}, '#{params['jfsj']}', '#{params['jy']}', #{params['kdmj']}, '#{params['kf']}', #{params['kh_id']}, '#{params['khdh']}', '#{params['khgj']}', '#{params['khlxr']}', '#{params['khsfz']}', '#{params['khxm']}', #{params['lc']}, #{params['length']}, '#{params['lx']}', '#{params['mpdh']}', '#{params['pq']}', #{params['sdj']}, #{params['ssmj']}, #{params['szj']}, '#{params['tax']}', #{params['width']}, '#{params['wtbh']}', '#{params['wtdq']}', '#{params['wtfs']}', '#{params['wymc']}', #{params['wyzd_id']}, #{params['yg_id']}, '#{params['ygbm']}', '#{params['ygxm']}', '#{params['yt']}', '#{params['yzf']}', #{params['zc']}, #{params['zdj']}, #{params['zmj']}, '#{params['zt']}', #{params['zzj']}, TIMESTAMP '#{params['update_at']}');")
      
      Users.find_by_sql("update wydd set published = true, cs=#{wydd['cs']}, cc=#{wydd['cc']}, kk=#{wydd['kk']}, gg=#{wydd['gg']},bgmj=#{wydd['bgmj']}, ssmj=#{wydd['ssmj']}, kdmj=#{wydd['kdmj']}, cfmj=#{wydd['cfmj']},bz='#{wydd['bz']}' where id = #{wydd['id']};")
      
    end    

	  render :text => 'Success'
	end
	
	def get_kybh
	  user = Users.find_by_sql("select distinct id, kybh from qzxx where kybh like '%#{params['query']}%' order by kybh;")
	  if user.size > 0
  	  txt = "["
  	  for k in 0..user.size-1
  	    txt = txt + "['#{user[k].id}','#{user[k].kybh}'],"
  	  end  
  	  txt = txt[0..-2] + "]"
	  else
	    txt = "[]"
	  end  
	  render :text => txt
	end

	def get_fybh
	  user = Users.find_by_sql("select distinct id, czbh, khxm from czxx where czbh like '%#{params['query']}%' order by czbh;")
	  if user.size > 0
  	  txt = "["
  	  for k in 0..user.size-1
  	    txt = txt + "['#{user[k].id}','#{user[k].czbh}(#{user[k].khxm})'],"
  	  end  
  	  txt = txt[0..-2] + "]"
	  else
	    txt = "[]"
	  end  
	  render :text => txt
	end	
	
	def get_reminder
	  id = params['id']
	  user = Users.find_by_sql("select g.message, g.txnr, z.kybh, z.khxm, users.mobile from qzgj as g inner join qzxx as z on qzxx_id = z.id inner join users on users.id = z.yg_id where z.yg_id = #{id};")
	  txt = '<h1>===客源提醒===</h1>'
	  for k in 0..user.size-1
	    ss = user[k]
	    txt = txt + "<font color=\"green\">【客源编号:】#{ss['kybh']}</font><br />【客户姓名:】#{ss['khxm']}<br />【提醒内容:】<font color=\"red\">#{ss['txnr']}</font><br />【跟进内容:】#{ss['message']}<br /><br />"  
	  end
	  
	  user = Users.find_by_sql("select g.message, g.txnr, z.czbh, z.wymc, users.mobile from czgj as g inner join czxx as z on czxx_id = z.id inner join users on users.id = z.yg_id  where z.yg_id = #{id};")
	  txt = txt + '<h1>===房源提醒===</h1>'
	  for k in 0..user.size-1
	    ss = user[k]
	    txt = txt + "<font color=\"green\">【房源编号:】#{ss['czbh']}</font><br />【物业名称:】#{ss['wymc']}<br />【提醒内容:】<font color=\"red\">#{ss['txnr']}</font><br />【跟进内容:】#{ss['message']}<br /><br />"  
	  end
	  
	  user = Users.find_by_sql("select * from qzxx where txzt = '提醒' and yg_id = #{id};")
	  txt = txt + '<h1>===客源到期提醒===</h1>'
	  for k in 0..user.size-1
	    ss = user[k]
	    txt = txt + "<font color=\"green\">【客源编号:】#{ss['kybh']}</font><br />【客户姓名:】#{ss['khxm']} 需及时处理。<br /><br />"  
	  end	  

	  user = Users.find_by_sql("select * from czxx where txzt = '提醒' and yg_id = #{id};")
	  txt = txt + '<h1>===房源到期提醒===</h1>'
	  for k in 0..user.size-1
	    ss = user[k]
	    txt = txt + "<font color=\"green\">【房源编号:】#{ss['czbh']}</font><br />【客户姓名:】#{ss['wymc']} 需及时处理。<br /><br />"  
	  end
	  	  
	  render :text => txt
	end
	
	def get_detail_user
	  user = Users.find_by_sql("select * from users where login='#{params['name']}';");
	  render :text => user[0].to_json
	end
	
	def get_detail_client
	  user = Users.find_by_sql("select * from cusomter id='#{params['id']}';");
	  render :text => user[0].to_json
	end
	
	def trans_czxx
	  params['update_at'] = Time.now.strftime('%Y-%m-%d %H:%M:%S')
	  
	  user = Users.find_by_sql("select * from users where login='#{params['name']}';")[0]
	  Users.find_by_sql("update czxx set yg_id=#{user.id}, ygbm='#{user.bm}', ygxm='#{user.login}', update_at=TIMESTAMP '#{params['update_at']}', txzt = '正常' where id=#{params['id']};")
	  
	  render :text => 'Sucess'
	end  

	def trans_qzxx
	  params['update_at'] = Time.now.strftime('%Y-%m-%d %H:%M:%S')
	  
	  user = Users.find_by_sql("select * from users where login='#{params['name']}';")[0]
	  Users.find_by_sql("update qzxx set yg_id=#{user.id}, ygbm='#{user.bm}', ygxm='#{user.login}', update_at=TIMESTAMP '#{params['update_at']}', txzt = '正常' where id=#{params['id']};")
	  
	  render :text => 'Sucess'
	end  
	
	def get_prevtree
		text = []
		node = params["node"].chomp
		if node == "root"
			data = Users.find_by_sql("select distinct id, login from users where login != 'admin';")
			data.each do |dd|
				text << {:text => dd["login"], :id => dd["id"], :iconCls	 => "user", :leaf => true}
			end
		end
		render :text => text.to_json
	end	 
	

	def get_prev
	  if params['yg_id'].nil? || params['yg_id'] == ""
	    txt = "{results:0,rows:[]}"	 
	  else
  		user =	Users.find_by_sql("select * from prev where yg_id=#{params['yg_id']};")
  		size = user.size;
  		if size > 0 
  			txt = "{results:#{size},rows:["
  			for k in 0..user.size-1
  				txt = txt + user[k].to_json[9..-2] + ','
  			end
  			txt = txt[0..-2] + "]}"
  		else
  			txt = "{results:0,rows:[]}"	 
  		end
  	end		 
		render :text => txt
	end
	
	
	#store of qy_store (地区编号)
	def get_qy
	  user = Users.find_by_sql("select * from code where codetype = '地区编号' order by code;")
	  if user.size > 0
  	  txt = "["
  	  for k in 0..user.size-1
  	    txt = txt + "['#{user[k].code}','#{user[k].codename}'],"
  	  end  
  	  txt = txt[0..-2] + "]"
	  else
	    txt = "[]"
	  end  
	  render :text => txt
	end

  #store of xxly_store (信息来源)
	def get_xxly
	  user = Users.find_by_sql("select * from code where codetype = '信息来源' order by code;")
	  if user.size > 0
  	  txt = "["
  	  for k in 0..user.size-1
  	    txt = txt + "['#{user[k].code}','#{user[k].codename}'],"
  	  end  
  	  txt = txt[0..-2] + "]"
	  else
	    txt = "[]"
	  end  
	  render :text => txt
	end	
	
	#store of xfdj_store (消防等级)
	def get_xfdj
	  user = Users.find_by_sql("select * from code where codetype = '消防等级' order by code;")
	  if user.size > 0
  	  txt = "["
  	  for k in 0..user.size-1
  	    txt = txt + "['#{user[k].code}','#{user[k].codename}'],"
  	  end  
  	  txt = txt[0..-2] + "]"
	  else
	    txt = "[]"
	  end  
	  render :text => txt
	end
	
	#store of cfms_store (厂房模式)
	def get_cfms
	  user = Users.find_by_sql("select * from code where codetype = '厂房模式' order by code;")
	  if user.size > 0
  	  txt = "["
  	  for k in 0..user.size-1
  	    txt = txt + "['#{user[k].code}','#{user[k].codename}'],"
  	  end  
  	  txt = txt[0..-2] + "]"
	  else
	    txt = "[]"
	  end  
	  render :text => txt
	end
	
	#store of gsgm_store (公司规模)
	def get_gsgm
	  user = Users.find_by_sql("select * from code where codetype = '公司规模' order by code;")
	  if user.size > 0
  	  txt = "["
  	  for k in 0..user.size-1
  	    txt = txt + "['#{user[k].code}','#{user[k].codename}'],"
  	  end  
  	  txt = txt[0..-2] + "]"
	  else
	    txt = "[]"
	  end  
	  render :text => txt
	end

	#store of syzlx_store (所有制类型)
	def get_syzlx
	  user = Users.find_by_sql("select * from code where codetype = '所有制类型' order by code;")
	  if user.size > 0
  	  txt = "["
  	  for k in 0..user.size-1
  	    txt = txt + "['#{user[k].code}','#{user[k].codename}'],"
  	  end  
  	  txt = txt[0..-2] + "]"
	  else
	    txt = "[]"
	  end  
	  render :text => txt
	end
	

	
	#store of zwbh_store (职位编号)
	def get_zwbh
	  user = Users.find_by_sql("select * from code where codetype = '职位编号' order by code;")
	  if user.size > 0
  	  txt = "["
  	  for k in 0..user.size-1
  	    txt = txt + "['#{user[k].code}','#{user[k].codename}'],"
  	  end  
  	  txt = txt[0..-2] + "]"
	  else
	    txt = "[]"
	  end  
	  render :text => txt
	end
	
	#store of khjb_store (客户级别)
	def get_khjb
	  user = Users.find_by_sql("select * from code where codetype = '客户级别' order by code;")
	  if user.size > 0
  	  txt = "["
  	  for k in 0..user.size-1
  	    txt = txt + "['#{user[k].code}','#{user[k].codename}'],"
  	  end  
  	  txt = txt[0..-2] + "]"
	  else
	    txt = "[]"
	  end  
	  render :text => txt
	end

	#store of khjb_store (客户类别)
	def get_khlb
	  user = Users.find_by_sql("select * from code where codetype = '客户类别' order by code;")
	  if user.size > 0
  	  txt = "["
  	  for k in 0..user.size-1
  	    txt = txt + "['#{user[k].code}','#{user[k].codename}'],"
  	  end  
  	  txt = txt[0..-2] + "]"
	  else
	    txt = "[]"
	  end  
	  render :text => txt
	end
	
	#store of users_store (本地用户)
	def get_users
	  user = Users.find_by_sql("select id, login from users where login != 'admin' order by id;")
	  if user.size > 0
  	  txt = "["
  	  for k in 0..user.size-1
  	    txt = txt + "['#{user[k].id}','#{user[k].login}'],"
  	  end  
  	  txt = txt[0..-2] + "]"
	  else
	    txt = "[]"
	  end  
	  render :text => txt
	end
		
	def upload
	 	params.each do |k,v|
			logger.debug("#{k} , #{v}")
			
			if k.include?("userfile")
  			logger.debug("#{v.path}")
  			filename = 'image_'+ActiveSupport::SecureRandom.hex(6)
  			while File.exists?("public/images/photos/#{filename}.jpg")
  				filename = 'image_'+ActiveSupport::SecureRandom.hex(6)
  			end	 
			
  			logger.debug("filename is #{filename}")
  			system("cp #{v.path} public/images/photos/#{filename}.jpg")
			
  			#make thumbnail 
  			#system("gdalwarp -ts 64 64 public/images/inspect/cache/#{filename}.jpg public/images/inspect/cache/#{filename}_thumb.jpg")
  			image = Image.import("public/images/photos/#{filename}.jpg") 
  			image.resize! 64, 64
  			image.export("public/images/photos/#{filename}_thumb.jpg")
				
  			#insert into database
  			params['create_at'] = Time.now.strftime('%Y-%m-%d %H:%M:%S')
        Users.find_by_sql("insert into photos (wyzd_id, loc, create_at, bz) values(#{params['pars']}, '#{filename}', TIMESTAMP '#{params['create_at']}', '#{params['bz']}');")
      end
        
		end
		render :text => "{\"success\":true, \"msg\":'File was successfully uploaded.'}"	
	end	
	
	def get_wyzd_photos
	  user = Users.find_by_sql("select * from photos where wyzd_id=#{params['wyzd_id']};")
	  txt = ""
	  for k in 0..user.size-1
	    loc, bz = user[k].loc, user[k].bz
	    txt = txt + "<a href=\"/images/photos/#{loc}.jpg\" class=\"highslide\" onclick=\"return hs.expand(this)\">\n<img src=\"/images/photos/#{loc}_thumb.jpg\" alt=\"#{bz}\" title=\"#{bz}\" \/>\n</a>\n"
	  end 
	  render :text => txt
	end

  def get_prev_code
	  if params['yg_id'].nil? || params['yg_id'] == ""
	    txt = "f".ljust(30).gsub(" ", "f")	 
	  else
  		user =	Users.find_by_sql("select * from prev where yg_id=#{params['yg_id']} order by id;")
  		size = user.size;
  		if size > 0 
  		  txt=""
        for k in 0..size-1 do
          ss = user[k]
          txt = txt +ss.addprev+ss.delprev+ss.modprev+ss.viewprev+ss.advprev
        end  
  		else
  			txt = "f".ljust(30).gsub(" ", "f") 
  		end
  	end		 
		render :text => txt
	end
	
	def save_prev_code
	  code, yg_id = params['code'], params['yg_id']
    ss = code[0..4]
    Users.find_by_sql("update prev set addprev='#{ss[0,1]}', delprev='#{ss[1,1]}',  modprev='#{ss[2,1]}', viewprev='#{ss[3,1]}', advprev='#{ss[4,1]}' where yg_id=#{yg_id} and tabname='系统管理';")
    ss = code[5..9]                                                        
    Users.find_by_sql("update prev set addprev='#{ss[0,1]}', delprev='#{ss[1,1]}',  modprev='#{ss[2,1]}', viewprev='#{ss[3,1]}', advprev='#{ss[4,1]}' where yg_id=#{yg_id} and tabname='客源管理';")
    ss = code[10..14]                        
    Users.find_by_sql("update prev set addprev='#{ss[0,1]}', delprev='#{ss[1,1]}',  modprev='#{ss[2,1]}', viewprev='#{ss[3,1]}', advprev='#{ss[4,1]}' where yg_id=#{yg_id} and tabname='房源管理';")
    ss = code[15..19]                              
    Users.find_by_sql("update prev set addprev='#{ss[0,1]}', delprev='#{ss[1,1]}',  modprev='#{ss[2,1]}', viewprev='#{ss[3,1]}', advprev='#{ss[4,1]}' where yg_id=#{yg_id} and tabname='联系人';")
    ss = code[20..24]                           
    Users.find_by_sql("update prev set addprev='#{ss[0,1]}', delprev='#{ss[1,1]}',  modprev='#{ss[2,1]}', viewprev='#{ss[3,1]}', advprev='#{ss[4,1]}' where yg_id=#{yg_id} and tabname='排行榜';")
    ss = code[25..29]                           
    Users.find_by_sql("update prev set addprev='#{ss[0,1]}', delprev='#{ss[1,1]}',  modprev='#{ss[2,1]}', viewprev='#{ss[3,1]}', advprev='#{ss[4,1]}' where yg_id=#{yg_id} and tabname='日程安排';")

    render :text => 'Sucess'

	end
	
	def check_contrack_czbh
	  user = Users.find_by_sql("select address, lxr, gsmc, mobile from customer where khbh='#{params['czbh']}'")
	  render :text => user.to_json
	end
	
	def check_contrack_qzbh
	  user = Users.find_by_sql("select qzxx.id, customer.address, customer.lxr, customer.gsmc, customer.mobile from customer inner join qzxx on  qzxx.kh_id = customer.id where khbh='#{params['qzbh']}'")
	  render :text => user.to_json
	end
	
	def add_contract
	  # insert new
 	  if (params['id'].nil? || params['id']=='') 
	    Users.find_by_sql("insert into contract(hybh, czxx_id, qzxx_id, czbh, qzbh, qyrq, cz_name, cz_address, cz_lxr, cz_mobile, qz_name, qz_address, qz_lxr, qz_mobile, mianj, nianx, zuqi, zuj, jfsj, sqzfsj, djbzj, khsfz, qyr, bz) values ('#{params['hybh']}',   #{params['czxx_id']}, #{params['qzxx_id']}, '#{params['czbh']}', '#{params['qzbh']}', TIMESTAMP '#{params['qyrq']}', '#{params['cz_name']}', '#{params['cz_address']}', '#{params['cz_lxr']}',  '#{params['cz_mobile']}', '#{params['qz_name']}', '#{params['qz_address']}', '#{params['qz_lxr']}', '#{params['qz_mobile']}', '#{params['mianj']}', '#{params['nianx']}', '#{params['zuqi']}', '#{params['zuj']}', TIMESTAMP '#{params['jfsj']}', TIMESTAMP '#{params['sqzfsj']}', '#{params['djbzj']}', '#{params['khsfz']}', '#{params['qyr']}', '#{params['bz']}');".gsub("请选择", ""))
	    contract_id = Users.find_by_sql("select * from contract where czxx_id=#{params['czxx_id']} and qzxx_id=#{params['qzxx_id']};")[0].id
	    Users.find_by_sql("update czxx set contract_id=#{contract_id} where id=#{params['czxx_id']};")
	    Users.find_by_sql("update qzxx set contract_id=#{contract_id} where id=#{params['qzxx_id']};")
	  else #update 
	    Users.find_by_sql("update contract set  hybh='#{params['hybh']}', czxx_id=#{params['czxx_id']}, qzxx_id= #{params['qzxx_id']}, czbh='#{params['czbh']}', qzbh='#{params['qzbh']}', qyrq=TIMESTAMP '#{params['qyrq']}', cz_name='#{params['qz_name']}', cz_address='#{params['qz_address']}', cz_lxr='#{params['qz_lxr']}', cz_mobile='#{params['cz_mobile']}', qz_name='#{params['qz_name']}', qz_address='#{params['qz_address']}', qz_lxr= '#{params['qz_lxr']}', qz_mobile='#{params['qz_mobile']}',  mianj='#{params['mianj']}', nianx='#{params['nianx']}', zuqi='#{params['zuqi']}',  zuj='#{params['zuj']}', jfsj=TIMESTAMP '#{params['jfsj']}', sqzfsj=TIMESTAMP '#{params['sqzfsj']}', djbzj='#{params['djbzj']}', khsfz='#{params['khsfz']}', qyr='#{params['qyr']}', bz='#{params['bz']}' where id=#{params['id']};")
	  end
	  
	  user = Users.find_by_sql("select * from contract where czxx_id=#{params['czxx_id']} and qzxx_id=#{params['qzxx_id']};")  
	  render :text => user.to_json

	end
	
	def getContractById
	  contract_id = params['contract_id']
	  user=Users.find_by_sql("select * from contract where id = #{contract_id}")
	  render :text => user.to_json
	end 
	
	#{text:'王娅红', iconCls: 'user', handler:qz_click_handler}
	def get_czuser_menu
	  user = Users.find_by_sql("select login from Users where id > 1;")
	  if user.size > 0
  	  txt = "["
  	  for k in 0..user.size-1
  	    txt = txt + "{text:'#{user[k].login}', iconCls: 'user', handler:cz_click_handler},"
  	  end  
  	  txt = txt[0..-2] +"]"
	  else
	    txt = "[]"
	  end  
	  render :text => txt
	end
	
	def get_qzuser_menu
	  user = Users.find_by_sql("select login from Users where id > 1;")
	  if user.size > 0
  	  txt = "["
  	  for k in 0..user.size-1
  	    txt = txt + "{text:'#{user[k].login}', iconCls: 'user', handler:qz_click_handler},"
  	  end  
  	  txt = txt[0..-2] +"]"
	  else
	    txt = "[]"
	  end  
	  render :text => txt
	end
	
end
